from django.http import HttpResponse
from django.shortcuts import render
from users.models import PersonModel
from django.db.models import Max, Min, Sum, Avg, Count

# Create users views here.


def add_person(request):
    """ 增加一条或多条数据 """
    # 方式一
    # try:
    #     p = PersonModel()
    #     p.name = '张三'  # name字段有唯一约束无法重复插入（UNIQUE constraint failed: tb_person.name）
    #     p.age = 10
    #     p.save()  # 同步到数据库表中
    # except Exception as e:
    #     return HttpResponse(f'添加失败！{e}')

    # 方式二
    # try:
    #     p = PersonModel(name='李四', age=12)
    #     p.save()
    # except Exception as e:
    #     return HttpResponse(f'添加失败！{e}')

    # 方式三
    # try:
    #     PersonModel.objects.create(name='王五', age=16)
    # except Exception as e:
    #     return HttpResponse(f'添加失败！{e}')

    # 方式四
    # get_or_create方法不会报错，因为不存在就创建，存在就获取出来
    ret = PersonModel.objects.get_or_create(name='赵六', age=18)
    # ret = (<PersonModel: PersonModel object (4)>, True)
    # 如果之前没有数据则创建这个数据：返回的是一个元组，第一个是创建的对象，第二个是是否创建
    # 如果之前有数据则或许这个数据：返回的是一个元组，第一个是查到的元组，第二个是是否创建
    print(f'{ret = }')

    # 添加多条数据
    for i in range(1, 10):
        PersonModel.objects.create(name=f'小{i}子', age=10+i)

    return HttpResponse('添加成功！')


def delete_person(request):
    """ 删除数据 """
    # 1.先找到要删除的数据
    # 2.然后删除
    try:
        # 删除一条数据（其实就是只查询到一条）
        p = PersonModel.objects.first()
        p.delete()

        # 删除多条数据（其实就是查询到多条数据）
        PersonModel.objects.filter(age__gt=18).delete()

    except Exception as e:
        return HttpResponse(f'删除失败！{e}')

    return HttpResponse(f'删除成功！')


def update_person(request):
    """ 修改数据 """
    # 1.先找到要修改的数据
    # 2.然后修改
    try:
        # 修改一条数据
        p = PersonModel.objects.first()
        p.age = 66
        p.save()
        # p.save(update_fields=['age']) 告诉数据库只更新age列，提高效率
        # 修改多条数据
        PersonModel.objects.all().update(age=100)
    except Exception as e:
        return HttpResponse(f'修改失败！{e}')

    return HttpResponse('修改成功！')


def read_person(request):
    """ 查询数据 """

    # get() 按照条件查询出一个对象，如果没有这个对象会报错，如果有多个对象也会报错
    p1 = PersonModel.objects.get(id=10)
    p2 = PersonModel.objects.get(pk=10)   # pk是主键的意思
    # p3 = PersonModel.objects.get(age=18)  # 会报错 MultipleObjectsReturned
    # p3 = PersonModel.objects.get(age=0)  # 会报错 DoesNotExist
    print(p1, type(p1))
    print(p1.name, p1.age)
    print(p2.name, p2.age)

    # all() 获取所有数据
    persons = PersonModel.objects.all()
    print(persons, type(persons))  # 返回一个QuerySet查询集
    for p in persons:
        print(p, p.name)  # 查询集可以被遍历

    # first() 查询第一条数据，返回的是一个模型类实例对象
    p = PersonModel.objects.first()
    print(p.name, p.age)

    # last() 查询最后一条数据
    p = PersonModel.objects.last()
    print(p.name, p.age)

    # filter() 按照过滤条件来查找，如果不传入条件则和all一样
    persons = PersonModel.objects.filter()
    for p in persons:
        print(f'{p.name = }')
    persons = PersonModel.objects.filter(age__gt=14)
    persons = PersonModel.objects.filter(age__gte=14)
    persons = PersonModel.objects.filter(age__lt=14)
    persons = PersonModel.objects.filter(age__lte=14)
    persons = PersonModel.objects.filter(age=14)
    print(type(persons))  # filter返回值一定是个查询集
    # 查询集可以链式调用
    # persons.filter().filter().first()
    persons = PersonModel.objects.filter(age__in=[10, 11, 12])  # in
    print('in', persons)
    persons = PersonModel.objects.exclude(age__in=[10, 11, 12])  # not in
    print('not in', persons)
    persons = PersonModel.objects.filter(age__contains='3')
    print('like', persons[0].age)
    persons = PersonModel.objects.filter(name__contains='小')
    persons = PersonModel.objects.filter(name__regex='^Wx')  # regex正则匹配
    print('regex', persons)
    persons = PersonModel.objects.filter(name__iregex='^wx')  # 忽略大小写的正则匹配
    print('iregex', persons)
    persons = PersonModel.objects.filter(age__range=[10, 15])  # 闭区间
    print('range', persons)
    persons = PersonModel.objects.filter(name__startswith='wx')  # 查询以wx开头
    persons = PersonModel.objects.filter(name__istartswith='wx')  # 查询以wx开头，不区分大小写
    persons = PersonModel.objects.filter(name__endswith='wx')  # 查询以wx结尾
    persons = PersonModel.objects.filter(name__iendswith='wx')  # 查询以wx结尾，不区分大小写

    # exists() 判断查询集是否有数据
    print(persons.exists())

    # count() 统计查询集中数据对象个数
    print(persons.count())

    # values() 和 values_list()
    persons = PersonModel.objects.all()
    print(f'{persons = }')
    print(f'{list(persons) = }')     # 将查询集强制转换为列表
    print(f'{persons.values() = }')  # 列表套字典
    print(f'{persons.values("name") = }')  # 指定查询字段
    print(f'{persons.values_list()}')   # 列表套元组，只有值没有字段名称
    print(f'{persons.values_list("name", "age")}')  # 指定查询字段

    # 聚合函数： Max, Min, Sum, Avg, Count
    # from django.db.models import Max, Min, Sum, Avg, Count
    persons = PersonModel.objects.aggregate(Max('age'))
    print(persons)  # {'age__max': 66}
    persons = PersonModel.objects.aggregate(Min('age'))
    print(persons)  # {'age__min': 11}
    persons = PersonModel.objects.aggregate(Sum('age'))
    print(persons)  # {'age__sum': 200}
    persons = PersonModel.objects.aggregate(Avg('age'))
    print(persons)  # {'age__avg': 20.0}
    persons = PersonModel.objects.aggregate(Count('age'))
    print(persons)  # {'age__count': 10}

    # order_by() 排序
    result = PersonModel.objects.all().order_by('id')  # 默认升序
    print([(p.id, p.age) for p in result])
    result = PersonModel.objects.all().order_by('age', '-id')  # 先按照age升序，如果age相同在按照id降序
    print([(p.id, p.age) for p in result])

    return HttpResponse('查询成功！')
